package dao.impl;

import dao.StaffsDao;
import entity.Staff;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.DruidUtil;

import java.sql.SQLException;
import java.util.List;

public class StaffDaoImpl4DbUtils implements StaffsDao {
    QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());

    @Override
    public int insert(Staff staffs) {
        // 第一：定义要操作的SQL语句
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?)";

        // 第二：执行 SQL 语句
        Object[] params = {staffs.getName(), staffs.getAge(), staffs.getPhone(), staffs.getStaPos()};
        try {
            // 不定长参数本质上是数组
            return qr.update(sql, params);
        } catch (SQLException e) {
            throw new RuntimeException("添加失败", e);
        }
    }


    @Override
    public int delete(String id) {
        String sql = "delete from staffs where id=?";
        try {
            return qr.update(sql, id);
        } catch (SQLException e) {
            throw new RuntimeException("删除失败", e);
        }
    }


    @Override
    public int update(Staff s) {
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=? where id=?";
        Object[] params = {s.getName(), s.getAge(), s.getPhone(), s.getStaPos(), s.getId()};
        try {
            return qr.update(sql, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Staff> selectAll() {
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs ";
        try {
            return qr.query(sql, new BeanListHandler<>(Staff.class));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Staff> selectByPage(int currentPage, int pageSize) {
        String sql = "select id,name,age,phone,sta_pos,add_time,update_time from staffs limit ? offset ?";
        int offset = (currentPage - 1) * pageSize;
        ResultSetHandler<List<Staff>> beanListHandler = new BeanListHandler<>(Staff.class);
        try {
            return qr.query(sql, beanListHandler, pageSize, offset);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Staff selectById(int id) {
        String sql = "select id,name,age,phone,sta_pos as staPos,add_time as addTime,update_time as updateTime from staffs where id=?" ;
        try {
            return  qr.query(sql,new BeanHandler<>(Staff.class),id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    }

